package newtonERP.orm;

import java.sql.ResultSet;
import java.util.Collection;
import java.util.Hashtable;
import java.util.Vector;

import newtonERP.common.ListModule;
import newtonERP.module.AbstractOrmEntity;
import newtonERP.module.Module;
import newtonERP.module.exception.ModuleException;
import newtonERP.orm.exceptions.OrmException;
import newtonERP.orm.field.Field;
import newtonERP.orm.sgbd.AbstractSgbd;
import newtonERP.orm.sgbd.sqlite.SgbdSqlite;
import newtonERP.serveur.ConfigManager;
import newtonERP.taskManager.TaskManager;

/**
 * Basic class for the orm. It is used to put the objects in the databse using
 * SqLite3 and its java binding. The orm will receive an entity from which the
 * orm will perform various tasks such as generating the query and executing it
 * obviously. Then it's gonna send the query to the SgbdSqlite class to execute
 * it.
 * 
 * http://www.sqlite.org/lang_keywords.html
 * 
 * TODO: Drop a module (maybe a an array of the entities to drop?)
 * 
 * @author r3hallejo, r3lacasgu
 */
public class Orm
{
    @Deprecated
    private static AbstractSgbd sgbd = null;

    // vers le SGBD voulu

    /**
     * Alter table
     * 
     * @param entity the entity containing the new field
     * @param field the field to add
     * @return ?
     * @throws Exception si ça fail
     */
    public static ResultSet addColumnToTable(AbstractOrmEntity entity,
	    Field<?> field) throws Exception
    {
	return getSgbd().addColumnToTable(entity, field);
    }

    private static AbstractSgbd getSgbd() throws Exception
    {
	if (sgbd == null)
	    sgbd = buildSgbd();
	return sgbd;
    }

    private static AbstractSgbd buildSgbd() throws Exception
    {
	if (ConfigManager.getDbmsName().equals("sqlite"))
	    return new SgbdSqlite();// On cré la référence
	throw new Exception("Invalid DBMS type");
    }

    /**
     * Use only for complex queries. Use the select that takes only a vector of
     * entities instead
     * 
     * Method used to do search queries done from the views to the databse. The
     * search criterias that has been passed in parameter are a list of string
     * that has been generated by the view modules
     * 
     * @param searchEntity the entity that has to be researched
     * @param searchCriteriasParam the search criterias formatted into strings
     * @return a vector of ormizable entities
     * @throws Exception si ça fail
     */
    public static Vector<AbstractOrmEntity> select(
	    AbstractOrmEntity searchEntity, Vector<String> searchCriteriasParam)
	    throws Exception
    {
	return EntityCreator.createEntitiesFromResultSet(getSgbd().select(
		searchEntity, searchCriteriasParam), searchEntity);
    }

    /**
     * Uses the new where builder
     * 
     * Method used to do search queries done from the views to the databse. The
     * search criterias that has been passed in parameter are a list of string
     * that has been generated by the view modules
     * 
     * @param searchEntities the entities from which we will perform the search
     * @return the entities
     * @throws Exception si ça fail
     */
    public static Vector<AbstractOrmEntity> select(
	    Vector<AbstractOrmEntity> searchEntities) throws Exception
    {
	return EntityCreator.createEntitiesFromResultSet(getSgbd().select(
		searchEntities), searchEntities.get(0));
    }

    /**
     * @param searchEntity the single search entity
     * @return the entities that have been selected in the db
     * @throws Exception si ça fail
     */
    public static Vector<AbstractOrmEntity> select(
	    AbstractOrmEntity searchEntity) throws Exception
    {
	Vector<AbstractOrmEntity> searchEntities = new Vector<AbstractOrmEntity>();
	searchEntities.add(searchEntity);
	return select(searchEntities);
    }

    /**
     * Select. Use only if you are sure that it will return only 1 entity (or
     * that you want the first entity)
     * 
     * @param searchEntity the entity from which we will perform our search
     * @return the first entity from the result set
     * @throws Exception si ça fail
     */
    public static AbstractOrmEntity selectUnique(AbstractOrmEntity searchEntity)
	    throws Exception
    {
	return select(searchEntity).get(0);
    }

    /**
     * Select. Use only if you are sure that it will return only 1 entity (or
     * that you want the first entity)
     * 
     * @param searchEntities the entity from which we will perform our search
     * @return the first entity from the result set
     * @throws Exception si ça fail
     */
    public static AbstractOrmEntity selectUnique(
	    Vector<AbstractOrmEntity> searchEntities) throws Exception
    {
	return select(searchEntities).get(0);
    }

    /**
     * Method used to insert an entity in the databse based into the entity
     * passed in parameter
     * 
     * @param newEntity the entity to add
     * @return le id de clé primaire ajoutée
     * @throws Exception si ça fail
     */
    public static int insert(AbstractOrmEntity newEntity) throws Exception
    {
	int primaryKeyValue = getSgbd().insert(newEntity);

	if (primaryKeyValue != 0)
	    TaskManager.executeTasks(newEntity, primaryKeyValue);

	return primaryKeyValue;
    }

    /**
     * Insert an entity if no entity matches current field
     * 
     * @param newUniqueEntity New unique entity to insert
     * @throws Exception si insertion fail
     */
    public static void insertUnique(AbstractOrmEntity newUniqueEntity)
	    throws Exception
    {
	if (select(newUniqueEntity).size() < 1)
	    insert(newUniqueEntity);
    }

    /**
     * Method used to delete an entity from the database
     * 
     * @param searchEntity the entity to be researched
     * @param searchCriterias the search criterias for the where clause
     * @throws Exception si effacement fail
     */
    public static void delete(AbstractOrmEntity searchEntity,
	    Vector<String> searchCriterias) throws Exception
    {
	getSgbd().delete(searchEntity, searchCriterias);
    }

    /**
     * Uses the new where builder
     * 
     * Method used to delete an entity from the database
     * 
     * @param searchEntities the entities from which we will build our where
     *            clause
     * @throws Exception si effacement fail
     */
    public static void delete(Vector<AbstractOrmEntity> searchEntities)
	    throws Exception
    {
	getSgbd().delete(searchEntities);
    }

    /**
     * Using new where builder
     * 
     * Method used to delete an entity from the database
     * 
     * @param searchEntity the entity from which we will build our where
     * @throws Exception si effacement fail
     */
    public static void delete(AbstractOrmEntity searchEntity) throws Exception
    {
	Vector<AbstractOrmEntity> searchEntities = new Vector<AbstractOrmEntity>();
	searchEntities.add(searchEntity);
	delete(searchEntities);
    }

    /**
     * Method used to update / change an entity
     * 
     * @param entityContainingChanges the entity that has been changed and will
     *            be in the orm
     * @param searchCriterias the criterias used by the update
     * @throws Exception si update fail
     */
    public static void update(AbstractOrmEntity entityContainingChanges,
	    Vector<String> searchCriterias) throws Exception
    {
	getSgbd().update(entityContainingChanges, searchCriterias);
    }

    /**
     * Uses the new where builder
     * 
     * Method used to update / change an entity
     * 
     * @param searchEntities the entities from which we will build our where
     *            clause
     * @param entityContainingChanges the changes to apply
     * @throws Exception si update fail
     */
    public static void update(Vector<AbstractOrmEntity> searchEntities,
	    AbstractOrmEntity entityContainingChanges) throws Exception
    {
	getSgbd().update(searchEntities, entityContainingChanges);
    }

    /**
     * Uses the new where builder
     * 
     * Method used to update / change an entity
     * 
     * @param searchEntity the entities from which we will build our where
     *            clause
     * @param entityContainingChanges the changes to apply
     * @throws Exception si update fail
     */
    public static void updateUnique(AbstractOrmEntity searchEntity,
	    AbstractOrmEntity entityContainingChanges) throws Exception
    {
	getSgbd().updateUnique(searchEntity, entityContainingChanges);
    }

    /**
     * Creates the non-existent table from the modules in the database
     * @throws Exception remonte
     */
    public static void createNonExistentTables() throws Exception
    {
	Hashtable<String, String> modules = ListModule.getAllModules();

	for (String key : modules.keySet())
	{
	    try
	    {
		Module module = ListModule.getModule(key);
		createNonExistentTables(module);
	    } catch (ModuleException e)
	    {
		// PrintStackTrace nécéssaire pour afficher l'information de
		// l'exception précédente. Il faudrait mettre l'ancien
		// stackTrace dans le nouveau
		e.printStackTrace();
		throw new ModuleException(
			"Erreur à la construction de la requête pour créer les tables : "
				+ e.getMessage());
	    }
	}
    }

    private static void createNonExistentTables(Module module) throws Exception
    {
	Collection<AbstractOrmEntity> moduleEntities = module
		.getEntityDefinitionList().values();

	// For each entity in the list of module entities
	for (AbstractOrmEntity entity : moduleEntities)
	{
	    createTableForEntity(entity);
	    addMissingColumnsForEntity(entity);
	    createIndexesForEntity(entity);
	}
    }

    private static void addMissingColumnsForEntity(AbstractOrmEntity entity)
	    throws Exception
    {
	for (Field<?> field : entity.getFields())
	{
	    try
	    {
		getSgbd().addColumnToTable(entity, field);
	    } catch (OrmException e)
	    {
		System.out.println("Champ déjà dans entité");
	    }
	}
    }

    private static void createTableForEntity(AbstractOrmEntity entity)
	    throws Exception
    {
	getSgbd().createTableForEntity(entity);
    }

    private static void createIndexesForEntity(AbstractOrmEntity entity)
	    throws Exception
    {
	// On cré des index pour chaque clef étrangère
	for (String fieldName : entity.getFields().getKeyList())
	    if ((fieldName.endsWith("ID") && !fieldName.startsWith("PK"))
		    || fieldName.startsWith("system"))
		createIndex(entity.getSystemName(), fieldName);
    }

    /**
     * Sert à ajouter un index dans la table SQL de l'entité pour un field en
     * particulier
     * @param entity entité
     * @param field champ
     * @throws Exception si ça fail
     */
    public static void createIndex(AbstractOrmEntity entity, Field<?> field)
	    throws Exception
    {
	createIndex(entity.getSystemName(), field.getSystemName());
    }

    /**
     * Sert à ajouter un index dans la table SQL de l'entité pour un field en
     * particulier
     * @param entityName nom de l'entité
     * @param fieldName nom du field
     * @throws Exception si ça fail
     */
    public static void createIndex(String entityName, String fieldName)
	    throws Exception
    {
	getSgbd().createIndex(entityName, fieldName);
    }

    /**
     * Used to initialize the connection
     * @throws Exception si ça fail
     */
    public static void connect() throws Exception
    {
	getSgbd().connect();
    }

    /**
     * Used to disconnect from the db
     * @throws Exception si ça fail
     */
    public static void disconnect() throws Exception
    {
	getSgbd().disconnect();
    }

    /**
     * To execute a custom query
     * 
     * @param sqlQuery the executed
     * @throws Exception si exécution fail
     */
    public static void executeCustomQuery(String sqlQuery) throws Exception
    {
	getSgbd().execute(sqlQuery, OrmActions.OTHER);
    }

    /**
     * @param entitySystemName nom système d'une entité
     * @return true si l'entité a une table dans la base de donnée, sinon false
     * @throws Exception si ça fail
     */
    public static boolean isEntityExists(String entitySystemName)
	    throws Exception
    {
	return getSgbd().isEntityExists(entitySystemName);
    }

    /**
     * @param searchEntity entité de recherche
     * @param searchCriteriasParam critères de recherche
     * @param limit limite de résultats
     * @param offset offset de début de résultats
     * @return liste d'entités trouvées
     * @throws Exception si ça fail
     */
    public static Vector<AbstractOrmEntity> select(
	    AbstractOrmEntity searchEntity,
	    Vector<String> searchCriteriasParam, int limit, int offset)
	    throws Exception
    {
	return select(searchEntity, searchCriteriasParam, limit, offset, null);
    }

    /**
     * @param searchEntity entité de recherche
     * @return nombre d'occurence du type de l'entité de recherche
     * @throws Exception si ça fail
     */
    public static int count(AbstractOrmEntity searchEntity) throws Exception
    {
	return count(searchEntity, null);
    }

    /**
     * @param searchEntity entité de recherche
     * @param searchParameterList liste de paramètres de recherche
     * @return nombre d'occurence du type de l'entité de recherche
     * @throws Exception si ça fail
     */
    public static int count(AbstractOrmEntity searchEntity,
	    Vector<String> searchParameterList) throws Exception
    {
	return getSgbd().count(searchEntity, searchParameterList);
    }

    /**
     * Fait un backup de la DB si l'intervale de temps est assez grande
     * @throws Exception si ça fail
     */
    public static void doBackupIfTimeIntervalAllows() throws Exception
    {
	long currentTime = BackupManager.getCurrentTime();
	long latestBackupTime = getSgbd().getLatestBackupTime();
	long desiredBackupTimeInterval = BackupManager
		.getDesiredBackupIntervalTime();

	if (currentTime - latestBackupTime > desiredBackupTimeInterval)
	    getSgbd().doBackup();
    }

    /**
     * @param searchEntity entité
     * @param searchParameters critère de recherche
     * @param limit limite
     * @param offset offset
     * @param orderBy ordre
     * @return liste d'entité
     * @throws Exception si ça fail
     */
    public static Vector<AbstractOrmEntity> select(
	    AbstractOrmEntity searchEntity, Vector<String> searchParameters,
	    int limit, int offset, String orderBy) throws Exception
    {
	return EntityCreator.createEntitiesFromResultSet(getSgbd().select(
		searchEntity, searchParameters, limit, offset, orderBy),
		searchEntity);
    }

    /**
     * @param entityAsType entity as a type reference or empty entity to fill
     *            data from orm
     * @param fieldName key
     * @param fieldValue value
     * @return new entity or found entity
     * @throws Exception si c¸a fail
     */
    public static AbstractOrmEntity getOrCreateEntity(
	    AbstractOrmEntity entityAsType, String fieldName, String fieldValue)
	    throws Exception
    {
	entityAsType.setData(fieldName, fieldValue);

	Vector<AbstractOrmEntity> entityList = entityAsType.get();

	if (entityList.size() > 0)
	{
	    return entityList.get(0);
	}

	entityAsType.newE();
	return entityAsType;
    }

    /**
     * @param entityAsType entity as a type reference or empty entity to fill
     * @param fieldName1 key1
     * @param fieldValue1 value2
     * @param fieldName2 key2
     * @param fieldValue2 value2
     * @return new entity or found entity
     * @throws Exception si c¸a fail
     */
    public static AbstractOrmEntity getOrCreateEntity(
	    AbstractOrmEntity entityAsType, String fieldName1,
	    String fieldValue1, String fieldName2, String fieldValue2)
	    throws Exception
    {
	entityAsType.setData(fieldName1, fieldValue1);
	entityAsType.setData(fieldName2, fieldValue2);

	Vector<AbstractOrmEntity> entityList = entityAsType.get();

	if (entityList.size() > 0)
	{
	    return entityList.get(0);
	}

	entityAsType.newE();
	return entityAsType;
    }

    /**
     * @param entityAsType entité en tant que référence de type ou entité vide
     *            pour ajouter des champs
     * @param fieldName1 key1
     * @param fieldValue1 value1
     * @param fieldName2 key2
     * @param fieldValue2 value2
     * @throws Exception si ça fail
     */
    public static void delete(AbstractOrmEntity entityAsType,
	    String fieldName1, String fieldValue1, String fieldName2,
	    String fieldValue2) throws Exception
    {
	entityAsType.setData(fieldName1, fieldValue1);
	entityAsType.setData(fieldName2, fieldValue2);

	Vector<AbstractOrmEntity> entityList = entityAsType.get();

	if (entityList.size() > 0)
	{
	    entityList.get(0).delete();
	}

    }
}
